const Book = require('../models/Book')
const db = require('../db')
const _ = require('lodash')
const {debug} = require('../utils/constant')

// 检查电子书是否存在
function exists(book) {
    const {title, author, publisher} = book
    const sql = `select * from book where title='${title}' and author='${author}' and publisher='${publisher}'`
    return db.queryOne(sql)
}

// 移除服务端电子书和数据库数据
async function removeBook(book) {
    if(book){
        // 删除服务器上的文件
        book.reset()
        // 删除数据库的记录
        if(book.fileName){
            const removeBookSql = `delete from book where fileName='${book.fileName}'`
            const removeContentsSql = `delete from contents where fileName='${book.fileName}'`
            await db.querySql(removeBookSql)
            await db.querySql(removeContentsSql)
        }
    }
}

// 往 数据库的目录表中添加目录
async function insertContents(book) {
    const contents = book.getContents()
    if(contents && contents.length >0){
        for(let i=0;i<contents.length;i++){
            const content = contents[i]
            const _content = _.pick(content, [
                'fileName',
                'id',
                'href',
                'order',
                'level',
                'text',
                'label',
                'pid',
                'navId'
            ])
            await db.insert(_content,'contents')
        }
    }
}

// 添加电子书
function insertBook(book) {
    return new Promise(async (resolve,reject)=>{
        try{
            // 验证book 是Book的实例
            if(book instanceof Book){
                const result =await exists(book)
                console.log('result===>',result)
                if(result){
                    await removeBook(book)
                    reject(new Error('电子书已存在'))
                }else{
                    // 往book表中添加book
                    // console.log('book.toDb()===>',book.toDb())
                    await db.insert(book.toDb(),'book')
                    // 往目录表中添加
                    await insertContents(book)
                    resolve()
                }
            }else{
                reject(new Error('添加的图书对象不合法'))
            }
        }catch(e){
            reject(e)
        }
    })
}

// 获取电子书信息
function getBook(fileName){
    return new Promise(async (resolve, reject) => {
        // resolve({fileName})
        const bookSql = `select * from book where fileName='${fileName}'`
        const contentsSql = `select * from contents where fileName='${fileName}' order by \`order\``
        const book = await db.queryOne(bookSql)
        const contents = await db.querySql(contentsSql)
        if(book){
            book.cover = Book.getCoverUrl(book)
            book.contentsTree = Book.getContentsTree(contents)
            resolve(book)
        }else{
            reject(new Error('电子书不存在'))
        }
    })
}

//update更新电子书
function updateBook(book){
    return new Promise(async (resolve, reject) => {
        try{
            if(book instanceof Book){
                const result = await getBook(book.fileName)
                if(result){
                    const model = book.toDb()
                    await db.update(model, 'book', `where fileName='${book.fileName}'`)
                    resolve()
                }
            }else{
                reject(new Error('添加的图书对象不合法'))
            }
        }
        catch(e){
         reject(e)
        }
    })
}


// 获取电子书分类
async function getCategory(){
    const categoryList = []
    const sql = 'select * from category order by category asc'
    const result = await db.querySql(sql);
    result.forEach(item=>{
        categoryList.push({
            label:item.categoryText,
            value:item.category,
            num:item.num
        })
    })
    // 这里直接return 是因为async函数的返回默认是promise对象
    return categoryList
}

async function listBook(query){
    debug && console.log(query)
    const {
        category,
        author,
        title,
        sort,
        page = 1,
        pageSize = 10
    } = query
    const offset = (page - 1) * pageSize
    let bookSql = 'select * from book'
    let where = 'where'
    title && (where = db.andLike(where, 'title', title))
    author && (where = db.andLike(where, 'author', author))
    category && (where = db.and(where, 'categoryText', category))
    if(where !== 'where'){
        bookSql = `${bookSql} ${where}`
    }
    if(sort){
        const symbol = sort[0]
        const column = sort.slice(1,sort.length)
        const order = symbol === '+'? 'asc':'desc'
        bookSql = `${bookSql} order by \`${column}\` ${order}`
    }
    // 返回总条数
    let countSql = `select count(*) as count from book`
    if(where !== 'where'){
        countSql = `${countSql} ${where}`
    }
    const count = await db.querySql(countSql)
    console.log('count', count)

    bookSql = `${bookSql} limit ${pageSize} offset ${offset}`
    const list = await db.querySql(bookSql)
    list.forEach(book=>book.cover = Book.getCoverUrl(book))
    return {list,count:count[0].count,page,pageSize}
}

function deleteBook(fileName){
    return new Promise(async (resolve,reject)=>{
        let book = await getBook(fileName)
        if(book){
            if(+book.updateType === 0){
                reject(new Error('内置电子书不能删除'))
            }else{
                const bookObj = new Book(null,book)
                const sql = `delete from book where fileName='${fileName}'`
                // 删除表中的记录
                db.querySql(sql).then(()=>{
                    // 删除nginx服务器上的文件
                    bookObj.reset()
                    resolve()
                })
            }
        }else{
            reject(new Error('电子书不存在'))
        }
    })
}

module.exports={
    insertBook,
    getBook,
    updateBook,
    getCategory,
    listBook,
    deleteBook
}